<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class StatisticsOtherCollect extends AdminController {

	function __construct() {
		parent::__construct();
		$this -> load-> model('Student_feedback_model');
		$this -> load-> model('Supervisor_feedback_model');
		$this -> load-> model('Admin_model');
		$this -> load-> model('Table1_model');
		$this -> load-> model('Table2_model');
		$this -> load-> model('Table3_model');
		$this -> load-> model('Table4_model');
		$this -> load-> model('Table5_model');
		$this -> load-> model('Table6_model');
	}

	function Index() {	
		$this->load->view('admin/statisticsOtherCollectList');
	}

	function getDatas($params = array(), $isExport = false) {
		if (isset($_POST['type']) && $_POST['type'] == 1) {//其他材料反馈报表
			$this->statisticsOtherDept($params,$isExport);

		}else{//个人其他材料反馈报表

			$this->statisticsOther($params,$isExport);
		}
	
	}


	//个人其他材料
	function statisticsOther($params = array(), $isExport = false){
		$connar = array();
		$params = $_POST;
		if (!$isExport) {			
			$connar = $this -> Admin_model -> pickPages($connar, $params);
		}	
		$connar['items']="DISTINCT (db_admin.id),usercode,username";
		$connar['join'][0]=array('db_table1','db_table1.admin_id=db_admin.id');
		$connar['join'][1]=array('db_table2','db_table2.admin_id=db_admin.id');
		$connar['join'][2]=array('db_table3','db_table3.admin_id=db_admin.id');
		$connar['join'][3]=array('db_table4','db_table4.admin_id=db_admin.id');
		$connar['join'][4]=array('db_table5','db_table5.admin_id=db_admin.id');
		$connar['join'][5]=array('db_table6','db_table6.admin_id=db_admin.id');
		$connar['where']=$this->sqlEscape('db_admin.status<>?? and (db_table1.admin_id IS NOT NULL OR db_table2.admin_id IS NOT NULL OR db_table3.admin_id IS NOT NULL OR db_table4.admin_id IS NOT NULL 	OR db_table5.admin_id IS NOT NULL)',array(CommonStatus::Deleted[XPHEnum::Value]));
		$role=explode(',',getSess()['sub_role_id']);
		array_push($role,getSess()['role_id']);
		if(in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
			$connar['where'].=' and 1=1 ';
		}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
			$connar['where'].=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
		}else if(in_array(WebRole::SchoolSupervisor[XPHEnum::Value], $role) || in_array(WebRole::UnitSupervisor[XPHEnum::Value], $role) || in_array(WebRole::ExternalSupervisor[XPHEnum::Value], $role)||in_array(WebRole::SupervisorDirector[XPHEnum::Value], $role)){
			$connar['where'].=$this->sqlEscape(' and db_admin.id=?? ',array(getSess()['id']));
		}
		if (isset($params['dept_name'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and dept_name like "%??%"',array($params['dept_name']));
		}else if (isset($_POST['dept_name'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and dept_name like "%??%"',array($_POST['dept_name']));
		}
		if (isset($params['username'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and username like "%??%"',array($params['username']));
		}else if (isset($_POST['username'])) {
			$connar['where'].=$this ->sqlLikeEscape(' and username like "%??%"',array($_POST['username']));
		}
		if (isset($params['level']) && $params['level']!=-1) {
			if($params['level']==FeedBackSubType::School[XPHEnum::Value]){
				$level=WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if($params['level']==FeedBackSubType::Unit[XPHEnum::Value]){
				$level=WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'].=$this ->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))',array($level,$level));
		}else if (isset($_POST['level']) && $_POST['level']!=-1) {
			if($_POST['level']==FeedBackSubType::School[XPHEnum::Value]){
				$level=WebRole::SchoolSupervisor[XPHEnum::Value];
			}
			if($_POST['level']==FeedBackSubType::Unit[XPHEnum::Value]){
				$level=WebRole::UnitSupervisor[XPHEnum::Value];
			}
			$connar['where'].=$this ->sqlEscape(' and (role_id=?? or find_in_set(??,sub_role_id))',array($level,$level));
		}	

		if (isset($params['start_time']) && isset($params['end_time'])) {
			$params['start_time'] = $params['start_time'].'00:00:00';
			$_POST['start_time'] = $_POST['start_time'].'00:00:00';
			$params['end_time'] = $params['end_time'].'23:59:59';
			$_POST['end_time'] = $_POST['end_time'].'23:59:59';
			// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
			// $connar['where'].=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
		}
		

		if (!$isExport) {
			$data = $this -> Admin_model -> get_page_list($connar);
			// 重新计算条数
			$count=$this->Admin_model->query("SELECT count(*) count from (SELECT DISTINCT 	db_admin.`usercode`,db_admin.`id`,	db_admin.`username` FROM `db_admin` LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id  LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id WHERE ".$connar['where'].")a");
			$data['count']=$count[0]['count'];
			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);
		} else {
			$data["ret"] = $this -> Admin_model -> get_list_full($connar);
		}

		$adminlist = array();
		foreach ($data['ret'] as $k => $v) {	
			// 反馈次数
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$where=' and feedback.created>'.strtotime($params['start_time']).' and feedback.created<'.strtotime($params['end_time']);
			}else if(isset($_POST['start_time']) && isset($_POST['end_time'])){
				$where=' and feedback.created>'.strtotime($_POST['start_time']).' and feedback.created<'.strtotime($_POST['end_time']);
			}else{
				$where='';
			}
			$feedbackTime=$this -> Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=".CommonStatus::UnDeleted[XPHEnum::Value]." and db_admin.id=".$v['id'].$where);

			$data['ret'][$k]['feedbackTime']=$feedbackTime[0]['feedbackTime'];	
			// 教学督导专题教研次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table1Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table1Time=$this-> Table1_model->get_list_full($connar1);
			$data['ret'][$k]['table1Time']=$table1Time[0]['table1Time'];
			// 督导教学巡(检)查情况记录表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table2Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}
			$table2Time=$this-> Table2_model->get_list_full($connar1);
			$data['ret'][$k]['table2Time']=$table2Time[0]['table2Time'];
			// 督导巡考情况记录表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table3Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table3Time=$this-> Table3_model->get_list_full($connar1);
			$data['ret'][$k]['table3Time']=$table3Time[0]['table3Time'];
			// 教学状态专项检查表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table4Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table4Time=$this-> Table4_model->get_list_full($connar1);
			$data['ret'][$k]['table4Time']=$table4Time[0]['table4Time'];
			// 学生顶岗实习巡查次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table5Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table5Time=$this-> Table5_model->get_list_full($connar1);
			$data['ret'][$k]['table5Time']=$table5Time[0]['table5Time'];
			// 教学信息员专题调查表次数
			$connar1['where']=$this->sqlEscape('admin_id=??',array($v['id']));
			$connar1['items']='count(*) table6Time';
			if (isset($params['start_time']) && isset($params['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
			}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
				$connar1['where'].=$this ->sqlEscape(' and created>?? and created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
			}
			$table6Time=$this-> Table6_model->get_list_full($connar1);
			$data['ret'][$k]['table6Time']=$table6Time[0]['table6Time'];

			 $adminlist[$k]['admin_id'] = $v['id'];
			 $adminlist[$v['id']]['count1'] = $table1Time[0]['table1Time'];
			 $adminlist[$v['id']]['count2'] = $table2Time[0]['table2Time'];
			 $adminlist[$v['id']]['count3'] = $table3Time[0]['table3Time'];
			 $adminlist[$v['id']]['count4'] = $table4Time[0]['table4Time'];
			 $adminlist[$v['id']]['count5'] = $table5Time[0]['table5Time'];
			 $adminlist[$v['id']]['count6'] = $table6Time[0]['table6Time'];

		}

		// $this->otherStatistics($adminlist); //同步老师的反馈数据

		if (!$isExport) {
			echo json_encode($data);			
		} else {
			// return $data["ret"];			
			$this-> load ->library('PHPExcel/PHPExcel');
	        $excel = new PHPExcel();
	        //公共样式
	        $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
	        $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
	        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

	        $x_line=0;
	        $y_line=1;

			//标题
	        $title=array($_POST['start_time'].'至'.$_POST['end_time'],'工号','姓名','反馈次数','教学督导专题调研次数','督导教学巡(检)查情况记录次数','督导巡考情况记录次数','教学状态专项检查次数','学生顶岗实习巡查次数','教学信息员专题调查次数');
	        foreach ($title as $k => $v) {
	        	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
	        	$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
	        	$x_line++;
	        }

	        $x_line=0;
	        $y_line=2;
	        //正式数据
	        
	        foreach ($data['ret'] as $k => $v) {        	
	        	foreach ($v as $k1 => $v1) {
	        		if($k1=='id'){
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
	        		}else{
	        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);
	        		}
	        		
	        		$x_line++;
	        	}
	        	$x_line=0;
	        	$y_line++;
	        }
	        $write = new PHPExcel_Writer_Excel2007($excel); 
	       	$director = "upload/export/" . date('Ymd', time()) . "/";
	        if (!file_exists($director)) {
	            //检查是否有该文件夹，如果没有就创建，并给予最高权限
	            mkdir($director, 0777);
	        }        
			$file_name ='个人其他材料反馈报表'.date('Ymd').'.xlsx';       	
	       	$write->save($director.iconv('utf-8', 'gb2312', $file_name));
			exit(retJson('返回地址',true,$director.$file_name));
		}

	}



	//其他材料反馈报表
	function statisticsOtherDept($params = array(), $isExport = false){
			$connar = array();
				if (!$isExport) {
					$params = $_POST;
					$connar = $this -> Admin_model -> pickPages($connar, $params);
				}
				// $connar['items']="id,dept_id,dept_name";
				// $connar['where']=$this->sqlEscape('status<>?? and  dept_id is not null',array(CommonStatus::Deleted[XPHEnum::Value]));
				// $connar['groupby']='dept_id';
				$where='where db_admin.status<>'.CommonStatus::Deleted[XPHEnum::Value].' and  db_admin.dept_id is not null and db_admin.dept_id<>0 AND (
							db_table1.admin_id IS NOT NULL
							OR db_table2.admin_id IS NOT NULL
							OR db_table3.admin_id IS NOT NULL
							OR db_table4.admin_id IS NOT NULL
							OR db_table5.admin_id IS NOT NULL
							OR db_table6.admin_id IS NOT NULL
						) and db_admin.dept_id<>0';
			$role=explode(',',getSess()['sub_role_id']);
			array_push($role,getSess()['role_id']);

			if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
				$where.=' and 1=1';
			}else{
				$where.=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
			}
				if (isset($params['start_time']) && isset($params['end_time'])) {
					// $where.=' and created>'.strtotime($params['start_time']).' and created<'.strtotime($params['end_time']);
					// $where.=$this->sqlEscape(" and db_table1.created>=?? and db_table1.created<??  and db_table2.created>=?? and db_table2.created<?? and db_table3.created>=?? and db_table3.created<?? and db_table4.created>=?? and db_table4.created<?? and db_table5.created>=?? and db_table5.created<?? and db_table6.created>=?? and db_table6.created<??",array(strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59'),strtotime($params['start_time']),strtotime($params['end_time'].' 23:59:59')));
				}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
					// $where.=' and created>'.strtotime($_POST['start_time']).' and created<'.strtotime($_POST['end_time']);
				}
				if(!$isExport){
					if(isset($params['pageNo']) && isset($params['pageSize'])){			
						$pageNo=$_POST['pageNo']-1;
						$pageSize=$_POST['pageSize'];
					}else{
						$pageNo='1';
						$pageSize='20';
					}
				}
				if (!$isExport) {
					$data = $this -> Admin_model -> get_page_list($connar);
					// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.' limit '.$pageSize*$pageNo.','.$pageSize.') attend group by dept_id');
					// $data['ret']=$this -> Admin_model -> query('select * from(select id,dept_id,dept_name from db_admin '.$where.') attend  group by dept_id limit '.$pageSize*$pageNo.','.$pageSize);
					$data['ret']=$this -> Admin_model -> query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
						LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
						LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
						LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
						LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
						LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.') attend  group by dept_id 
						order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
					$count=$this -> Admin_model -> query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
						LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
						LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
						LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
						LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
						LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.' ) attend group by dept_id
						order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
					$data['count']=$count[0]['count'];
					$data['totalPage']=ceil($data['count']/$pageSize);
				} else {
					$data['ret']=$this -> Admin_model -> query('select * from(select distinct(db_admin.id),dept_id,dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
					LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
					LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
					LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
					LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id
					LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.') attend  group by dept_id 
					order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部")');
					$count=$this -> Admin_model -> query('select count(*) count from (select * from (select distinct(db_admin.id),db_admin.dept_id,db_admin.dept_name from db_admin LEFT JOIN db_table1 ON db_admin.id = db_table1.admin_id
					LEFT JOIN db_table2 ON db_admin.id = db_table2.admin_id
					LEFT JOIN db_table3 ON db_admin.id = db_table3.admin_id
					LEFT JOIN db_table4 ON db_admin.id = db_table4.admin_id
					LEFT JOIN db_table5 ON db_admin.id = db_table5.admin_id 
					LEFT JOIN db_table6 ON db_admin.id = db_table6.admin_id '.$where.' ) attend group by dept_id
					order by field(dept_name,"电气电子工程分院","机械工程分院","建筑工程分院","设计与艺术分院","财经分院","交通分院","商贸分院","鉴湖分院","人文社科部"))a');
				}
				foreach ($data['ret'] as $k => $v) {
					// 反馈次数身份条件筛选
					if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
						$where_role=' and 1=1';
					}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
						$where_role=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
					}else{
						$where_role=$this->sqlEscape(' and creator=?? ',array(getSess()['id']));
					}
					
					// 反馈次数
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$where=' and feedback.created>'.strtotime($params['start_time']).' and feedback.created<'.strtotime($params['end_time']);
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$where=$this->sqlEscape(' and feedback.created>?? and feedback.created<??',array(strtotime($_POST['start_time']."00:00:00"),strtotime($_POST['end_time']."23:59:59")));
					}else{
						$where='';
					}
					$feedbackTime=$this -> Student_feedback_model->query("select count(*) feedbackTime from(select * from db_student_feedback union all select * from db_supervisor_feedback) feedback  left join db_admin on feedback.creator=db_admin.id where del_flag=".CommonStatus::UnDeleted[XPHEnum::Value]." and role_belong=".WebRole::Manage[XPHEnum::Value]." and feedback.status=".FeedBackStatus::HaveWrite[XPHEnum::Value]." and db_admin.dept_id=".$v['dept_id'].$where.$where_role);			

					$data['ret'][$k]['feedbackTime']=$feedbackTime[0]['feedbackTime'];
					
					// 其他材料次数身份条件筛选
					if(in_array(WebRole::Admin[XPHEnum::Value], $role)  || in_array(WebRole::Manage[XPHEnum::Value], $role) || in_array(WebRole::SchoolLeader[XPHEnum::Value], $role) ){
						$where_role=' and 1=1';
					}else if(in_array(WebRole::UnitSupervisorGroupLeader[XPHEnum::Value], $role)){
						$where_role=$this->sqlEscape(' and db_admin.dept_name=?? ',array(getSess()['dept_name']));
					}else{
						$where_role=$this->sqlEscape(' and admin_id=?? ',array(getSess()['id']));
					}

					// 教学督导专题教研次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table1.admin_id');
					$connar1['items']='count(*) table1Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table1.created>?? and db_table1.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this->sqlEscape(' and db_table1.created>?? and db_table1.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$table1Time=$this-> Table1_model->get_list_full($connar1);
					$data['ret'][$k]['table1Time']=$table1Time[0]['table1Time'];
					// 督导教学巡(检)查情况记录表次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table2.admin_id');
					$connar1['items']='count(*) table2Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table2.created>?? and db_table2.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this->sqlEscape(' and db_table2.created>?? and db_table2.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$table2Time=$this-> Table2_model->get_list_full($connar1);
					$data['ret'][$k]['table2Time']=$table2Time[0]['table2Time'];
					// 督导巡考情况记录表次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table3.admin_id');
					$connar1['items']='count(*) table3Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table3.created>?? and db_table3.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this->sqlEscape(' and db_table3.created>?? and db_table3.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$table3Time=$this-> Table3_model->get_list_full($connar1);
					$data['ret'][$k]['table3Time']=$table3Time[0]['table3Time'];
					// 教学状态专项检查表次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table4.admin_id');
					$connar1['items']='count(*) table4Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table4.created>?? and db_table4.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this->sqlEscape(' and db_table4.created>?? and db_table4.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$table4Time=$this-> Table4_model->get_list_full($connar1);
					$data['ret'][$k]['table4Time']=$table4Time[0]['table4Time'];
					// 学生顶岗实习巡查次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table5.admin_id');
					$connar1['items']='count(*) table5Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table5.created>?? and db_table5.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if (isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and db_table5.created>?? and db_table5.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$connar1['where'] .=$this -> sqlEscape(' and db_table5.status=??',array(CommonStatus::UnDeleted[XPHEnum::Value]));
					$table5Time=$this-> Table5_model->get_list_full($connar1);
					$data['ret'][$k]['table5Time']=$table5Time[0]['table5Time'];
					// 教学信息员专题调查表次数
					$connar1['where']=$this->sqlEscape('dept_id=??',array($v['dept_id']));
					$connar1['join']=array('db_admin','db_admin.id=db_table6.admin_id');
					$connar1['items']='count(*) table6Time';
					if (isset($params['start_time']) && isset($params['end_time'])) {
						$connar1['where'].=$this ->sqlEscape(' and table6.created>?? and table6.created<??',array(strtotime($params['start_time']),strtotime($params['end_time'])));
					}else if(isset($_POST['start_time']) && isset($_POST['end_time'])) {
						$connar1['where'].=$this->sqlEscape(' and table6.created>?? and table6.created<??',array(strtotime($_POST['start_time']),strtotime($_POST['end_time'])));
					}
					$connar1['where'].=$where_role;
					$table6Time=$this-> Table6_model->get_list_full($connar1);
					$data['ret'][$k]['table6Time']=$table6Time[0]['table6Time'];
				}
				// 去除都为0的记录
					// 		$ret=$data['ret'];
					// 		$data['ret']=array();
					// 		foreach ($ret as $k => $v) {
					// 			// $break=false;
					// 			// var_dump($v);
					// 			if(!($v['feedbackTime']=='0' && $v['table1Time']=='0'  && $v['table2Time']=='0' && $v['table3Time']=='0' && $v['table4Time']=='0' && $v['table5Time']=='0' && $v['table6Time']=='0')){

					// // var
					// 				// pri($data['ret'][$k]);
					// 				// unset($data['ret'][$k]);
					// 				// pri($data['ret']);
					// 				array_push($data['ret'], $v);
									
					// 			}else{
					// 				// var_dump('zl');
					// 				// exit;
					// 				$data['count']=$data['count']-1;
					// 			}
					// 			$data["totalPage"] = ceil($data["count"] / $params['pageSize']);
								
					// 		}
					// 		// 重置数组下标
					// 		$data['ret']=array_values($data['ret']);
				if (!$isExport) {

					$dept_count=0;
					$feedbackTime_count=0;
					$table1_count=0;
					$table2_count=0;
					$table3_count=0;
					$table4_count=0;
					$table5_count=0;
					$table6_count=0;
					// var_dump($data['ret'][0]['feedbackTime']);
					foreach ($data['ret'] as $k => $v) {
						$feedbackTime_count=$feedbackTime_count+$v['feedbackTime'];
						$table1_count+=$v['table1Time'];
						$table2_count+=$v['table2Time'];
						$table3_count+=$v['table3Time'];
						$table4_count+=$v['table4Time'];
						$table5_count+=$v['table5Time'];
						$table6_count+=$v['table6Time'];
					}
					$count = count($data['ret']);

					$data['ret'][$count]['dept_name']= '合计';
					$data['ret'][$count]['feedbackTime']=$feedbackTime_count;
					$data['ret'][$count]['table1Time']=$table1_count;
					$data['ret'][$count]['table2Time']=$table2_count;
					$data['ret'][$count]['table3Time']=$table3_count;
					$data['ret'][$count]['table4Time']=$table4_count;
					$data['ret'][$count]['table5Time']=$table5_count;
					$data['ret'][$count]['table6Time']=$table6_count;
					
					echo json_encode($data);
				} else {
					// return $data["ret"];
					$dept_count=0;
					$feedbackTime_count=0;
					$table1_count=0;
					$table2_count=0;
					$table3_count=0;
					$table4_count=0;
					$table5_count=0;
					$table6_count=0;
					// var_dump($data['ret'][0]['feedbackTime']);
					foreach ($data['ret'] as $k => $v) {
						$feedbackTime_count=$feedbackTime_count+$v['feedbackTime'];
						$table1_count+=$v['table1Time'];
						$table2_count+=$v['table2Time'];
						$table3_count+=$v['table3Time'];
						$table4_count+=$v['table4Time'];
						$table5_count+=$v['table5Time'];
						$table6_count+=$v['table6Time'];
					}			
					array_push($data['ret'],array('title'=>'合计','dept_count'=>$count[0]['count'],'feedback_count'=>$feedbackTime_count,'table1_count'=>$table1_count,'table2_count'=>$table2_count,'table3_count'=>$table3_count,'table4_count'=>$table4_count,'table5_count'=>$table5_count,'table6_count'=>$table6_count));
					$this-> load ->library('PHPExcel/PHPExcel');
			        $excel = new PHPExcel();
			        //公共样式
			        $excel->getActiveSheet()->getDefaultStyle()->getFont()->setSize(11); 
			        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
			        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setWrapText(true);
			        $excel->getActiveSheet()->getDefaultColumnDimension()->setWidth(23);
			        $excel->getActiveSheet()->getDefaultStyle()->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
			        $excel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);

			        $x_line=0;
			        $y_line=1;

					//标题
			        $title=array($_POST['start_time'].'至'.$_POST['end_time'],'学院','反馈次数','教学督导专题调研次数','督导教学巡(检)查情况记录次数','督导巡考情况记录次数','教学状态专项检查次数','学生顶岗实习巡查次数','教学信息员专题调查次数');
			        foreach ($title as $k => $v) {
			        	$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v);
			        	$excel->getActiveSheet()->getStyle(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line)->getFont()->setBold(true);   
			        	$x_line++;
			        }

			        $x_line=0;
			        $y_line=2;
			        //正式数据
			        
			        foreach ($data['ret'] as $k => $v) {
			        unset($v['dept_id']);
			        	foreach ($v as $k1 => $v1) {
			        		if($k1=='id'){
			        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$_POST['start_time'].'至'.$_POST['end_time']);
			        		}else{
			        			$excel->getActiveSheet()->setCellValue(PHPExcel_Cell::stringFromColumnIndex($x_line).$y_line,$v1);
			        		}
			        		
			        		$x_line++;
			        	}
			        	$x_line=0;
			        	$y_line++;
			        }
			        $write = new PHPExcel_Writer_Excel2007($excel); 
			       	$director = "upload/export/" . date('Ymd', time()) . "/";
			        if (!file_exists($director)) {
			            //检查是否有该文件夹，如果没有就创建，并给予最高权限
			            mkdir($director, 0777);
			        }        
					$file_name ='其他材料反馈报表'.date('Ymd').'.xlsx';       	
			       	$write->save($director.iconv('utf-8', 'gb2312', $file_name));
					exit(retJson('返回地址',true,$director.$file_name));
		}
	}



	//自动同步历史数据
	private function otherStatistics ($admin)
	{
		$this -> load-> model('Other_statistics_model');
		//pri($admin);
		$admin_list= array_column($admin,'admin_id');
		$admin_id = implode(",", $admin_list);

		// pri($admin_id);
		$connar1['where']=" admin_id in (".$admin_id.")" ;
		$other = $this->Other_statistics_model->get_list_full($connar1);

		if (empty($other)) {	
			foreach($admin_list as $k=>$v){
				$adminarr = $this->Admin_model->get_single(array('id' => $v));
				$add = array(
				'admin_id'  => $v,
				'username'  => $adminarr['username'],
				'dept_name' => $adminarr['dept_name'],
				'specialResearch'      => $admin[$v]['count1'],
				'patrol'      => $admin[$v]['count2'],
				'tourInformant'      => $admin[$v]['count3'],
				'specialInspection'      => $admin[$v]['count4'],
				'postPractice'      => $admin[$v]['count5'],
				'messenger'      => $admin[$v]['count6'],
			  );
			   $this->Other_statistics_model->add($add);
			}
		}else{
			foreach($other as $k=>$v){
				$update =array(
					'id' =>$v['id'],
					'specialResearch'      => $admin[$v['admin_id']]['count1'],
					'patrol'      => $admin[$v['admin_id']]['count2'],
					'tourInformant'      => $admin[$v['admin_id']]['count3'],
					'specialInspection'      => $admin[$v['admin_id']]['count4'],
					'postPractice'      => $admin[$v['admin_id']]['count5'],
					'messenger'      => $admin[$v['admin_id']]['count6'],
				);
				$this->Other_statistics_model->update($update);
			}
		}
	}
}
